package org.opsli.modulars.business.patrolRouteRecord.web;

import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.sun.jna.platform.win32.WinBase;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.opsli.api.base.result.ResultVo;
import org.opsli.api.wrapper.business.patrolRouteRecord.DailyInspectionReportModel;
import org.opsli.common.annotation.ApiRestController;
import org.opsli.core.base.controller.BaseRestController;
import org.opsli.core.persistence.Page;
import org.opsli.core.utils.UserUtil;
import org.opsli.modulars.business.app.web.ZkUtils;
import org.opsli.modulars.business.patrolRecordEXCEL.entity.PatrolRecord;
import org.opsli.modulars.business.patrolRouteRecord.entity.DailyInspectionReport;
import org.opsli.modulars.business.patrolRouteRecord.service.DailyInspectionReportService;
import org.opsli.modulars.business.patrolRouteRecord.service.impl.DailyInspectionReportServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author lh
 * 巡检日报表和巡检月报表，并导出Excel
 */
@Slf4j
@ApiRestController("/patrolRecord/dailyReport")
public class DailyInspectionReportController extends BaseRestController<DailyInspectionReport, DailyInspectionReportModel, DailyInspectionReportService> {
    @Autowired
    private DailyInspectionReportServiceImpl dirsi;
    double lnn;//巡检日报表所用维度
    double ltt;//巡检日报表所用经度
    double llnn;//巡检月报表所用维度
    double lltt;//巡检月报表所用维度
    private List listAll;//巡检日报表所用全局list
    private List listMonthAll;//巡检月报表所用全局list

    //巡检日报表
    @RequestMapping(value = "/daReport", method = RequestMethod.GET)
    public ResultVo<?> getDailyReport(Integer pageNo, Integer pageSize, String lastTime_BEGIN, String userName_LIKE, HttpServletRequest request) {
        DailyInspectionReportController da = new DailyInspectionReportController();
        String devid = UserUtil.getSysUser().getCompanyId();//当前公司ID
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
        Date date = null;
        String nowDate = null;
        //当进行查询操作的时候，对传入的时间进行处理
        try {
            if (StringUtils.isNotBlank(lastTime_BEGIN)) {
                date = df.parse(lastTime_BEGIN);
                long ts = date.getTime();
                long now = ts + 86400000;
                Date date0 = new Date(now);
                nowDate = df.format(date0);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //根据传参不同，调用不同的方法进而获取数据
        List l1 = null;
        if (StringUtils.isBlank(nowDate) && StringUtils.isNotBlank(userName_LIKE)) {
            l1 = dirsi.searchUserRoute(devid, userName_LIKE);//搜索时只输入姓名，搜索当天某人的巡线数据
        } else if (StringUtils.isBlank(nowDate) && StringUtils.isBlank(userName_LIKE)) {
            l1 = dirsi.getDailyReport(devid);//如果搜索时，时间和姓名都不输入，查询的是当天的巡线数据
        } else {
            l1 = dirsi.getUserRoute(devid, userName_LIKE, nowDate);//根据时间和姓名搜索数据
        }
        TreeSet s = new TreeSet<>();//查询出巡线记录后，一个人可能会有多条巡线记录，按照姓名进行分组
        List l3 = new ArrayList();
        for (int i = 0; i < l1.size(); i++) {
            DailyInspectionReport dai = (DailyInspectionReport) l1.get(i);
            if (StringUtils.isNotBlank(dai.getUserName())) {
                s.add(dai.getUserName());
            }
        }
        //将同一人的不同巡线记录存入list中
        for (Iterator iter = s.iterator(); iter.hasNext(); ) {
            String a1Name = (String) iter.next();
            List l2 = new ArrayList();
            for (int i = 0; i < l1.size(); i++) {
                DailyInspectionReport dai = (DailyInspectionReport) l1.get(i);
                String username = dai.getUserName();
                if (StringUtils.isNotBlank(username) && StringUtils.isNotBlank(a1Name)) {
                    if (username.equals(a1Name)) {
                        l2.add(l1.get(i));
                    }
                }
            }
            l3.add(l2);
        }
        List l5 = new ArrayList();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int a = 0; a < l3.size(); a++) {
            List l4 = (List) l3.get(a);
            DailyInspectionReport dd = new DailyInspectionReport();
            double patrolTimeSum = 0;//总共巡线时间
            double count = 0;
            double countSum = 0;
            for (int b = 0; b < l4.size(); b++) {
                DailyInspectionReport dai = (DailyInspectionReport) l4.get(b);
                if (b == 0) {
                    dd.setUserName(dai.getUserName());//巡检员
                    String patrolNumber = String.valueOf(l4.size());//获取巡线次数
                    dd.setRouteName(patrolNumber);//设置巡线次数
                }
                try {
                    String getBeginTime = dai.getBeginTime();//巡线开始时间
                    String getLastTime = dai.getLastTime();//巡线结束时间
                    if (StringUtils.isNotBlank(getBeginTime) && StringUtils.isNotBlank(getLastTime)) {
                        Date beginTime = sdf.parse(dai.getBeginTime());
                        Date lastTime = sdf.parse(dai.getLastTime());
                        if (lastTime.getTime() >= beginTime.getTime()) {
                            BigDecimal b1 = new BigDecimal(beginTime.getTime());
                            BigDecimal b2 = new BigDecimal(lastTime.getTime());
                            patrolTimeSum = (b2.subtract(b1).setScale(2, RoundingMode.HALF_UP).doubleValue()) / (3600 * 1000);
                            count = patrolTimeSum + count;//当天不同时间进行累加
                        }
                    }
                } catch (ParseException e) {
                    e.printStackTrace();
                }

                String trail = dai.getTrail();//巡线路径
                if (StringUtils.isNotBlank(trail)) {
                    JSONArray jsonArray = JSONArray.parseArray(trail);
                    for (int v = 0; v < jsonArray.size(); v++) {
                        if (v == 0) {
                            String ln = String.valueOf(jsonArray.getJSONObject(v).get("ln"));//获取路径第一个经纬度坐标
                            double lln = Double.parseDouble(ln);
                            String lt = String.valueOf(jsonArray.getJSONObject(v).get("lt"));
                            double llt = Double.parseDouble(lt);
                            lnn = lln;//存入变量，便于和下一个坐标进行计算
                            ltt = llt;
                        }
                        if (v > 0) {
                            String ln1 = String.valueOf(jsonArray.getJSONObject(v).get("ln"));//获取下一个坐标
                            double lln1 = Double.parseDouble(ln1);
                            String lt1 = String.valueOf(jsonArray.getJSONObject(v).get("lt"));
                            double llt1 = Double.parseDouble(lt1);
                            double d = da.getDistance(lnn, ltt, lln1, llt1);//根据经纬度计算距离
                            lnn = lln1;
                            ltt = llt1;
                            countSum = d + countSum;//距离累加
                        }
                    }
                    lnn = 0;
                    ltt = 0;
                }
            }
            String douStr = String.format("%.2f", countSum);
            dd.setTrail(String.valueOf(douStr));//设置总距离
            String cc = String.format("%.2f", count);
            dd.setLastTime(String.valueOf(cc));//设置总时间
            double distance = Double.parseDouble(douStr);
            double time = Double.parseDouble(cc);
            double avgSpeed = 0.0;
            if (time > 0.0) {
                 avgSpeed = distance / time;
            }
            String avg = String.format("%.2f",avgSpeed);
            dd.setAverageVelocity(avg);//巡检平均速度
            l5.add(dd);
        }

        //根据不同输入参数，查询巡点数据
        List l6 = null;
        if (StringUtils.isBlank(nowDate) && StringUtils.isNotBlank(userName_LIKE)) {
            l6 = dirsi.searchUserPoint(devid, userName_LIKE);
        } else if (StringUtils.isBlank(nowDate) && StringUtils.isBlank(userName_LIKE)) {
            l6 = dirsi.getPatrolPoint(devid);
        } else {
            l6 = dirsi.getUserPoint(devid, userName_LIKE, nowDate);
        }
        //根据姓名对巡点进行分组
        TreeSet s1 = new TreeSet<>();
        List l7 = new ArrayList();
        for (int i = 0; i < l6.size(); i++) {
            DailyInspectionReport dai = (DailyInspectionReport) l6.get(i);
            if (StringUtils.isNotBlank(dai.getUserName())) {
                s1.add(dai.getUserName());
            }
        }
        for (Iterator iter = s1.iterator(); iter.hasNext(); ) {
            String a1Name = (String) iter.next();
            List l8 = new ArrayList();
            for (int i = 0; i < l6.size(); i++) {
                DailyInspectionReport dai = (DailyInspectionReport) l6.get(i);
                String username = dai.getUserName();
                if (StringUtils.isNotBlank(username) && StringUtils.isNotBlank(a1Name)) {
                    if (username.equals(a1Name)) {
                        l8.add(l6.get(i));
                    }
                }
            }
            l7.add(l8);
        }
        List l9 = new ArrayList();
        for (int a = 0; a < l7.size(); a++) {
            List l10 = (List) l7.get(a);
            DailyInspectionReport dy = new DailyInspectionReport();
            int countType1 = 0;
            int countType2 = 0;
            int countType3 = 0;
            int countType4 = 0;
            if (l10.size() == 0) {
                dy.setStationName(String.valueOf(0));
            }
            for (int b = 0; b < l10.size(); b++) {
                DailyInspectionReport daily = (DailyInspectionReport) l10.get(b);
                if (b == 0) {
                    String patrolPointNumber = String.valueOf(l10.size());
                    dy.setStationName(patrolPointNumber);//设置巡点次数
                    dy.setUserName(daily.getUserName());//设置姓名
                }
                String typeNumber = daily.getStationType();
                if (typeNumber.equals("1")) {
                    countType1++;//主阀门次数累加
                }
                if (typeNumber.equals("2")) {
                    countType2++;//支阀门次数累加
                }
                if (typeNumber.equals("3")) {
                    countType3++;//调压柜次数累加
                }
                if (typeNumber.equals("4")) {
                    countType4++;//调压箱次数累加
                }
            }
            dy.setType1(String.valueOf(countType1));//设置主阀门次数
            dy.setType2(String.valueOf(countType2));//设置支阀门次数
            dy.setType3(String.valueOf(countType3));//设置调压柜次数
            dy.setType4(String.valueOf(countType4));//设置调压箱次数
            l9.add(dy);
        }

        da.getCombineDemo2(l9, l5);//将巡线数据和巡点数据，根据姓名进行合并
        listAll = l5;//将合并后的数据，存入公共的list，便于导出Excel

        //将合并的list进行分页
        Page page = new Page(pageNo, pageSize);
        int totalLength = l5.size(); //总的数据条数
        int totalPage = totalLength % pageSize == 0 ? totalLength / pageSize : totalLength / pageSize + 1; //总的页数
        List pageData = null;
        if (pageNo < totalPage) {
            int startIndex = (pageNo - 1) * pageSize;
            int endIndex = startIndex + pageSize - 1;
            pageData = l5.subList(startIndex, endIndex + 1);
        } else {
            int startIndex = (pageNo - 1) * pageSize;
            pageData = l5.subList(startIndex, totalLength);//最后一页
        }
        page.setList(pageData);
        page.setTotal(l5.size());
        return ResultVo.success(page.getPageData());
    }

    //巡检月报表
    @RequestMapping(value = "/daMonthReport", method = RequestMethod.GET)
    public ResultVo<?> getMonthReport(Integer pageNo, Integer pageSize, String lastTime_BEGIN, String userName_LIKE, HttpServletRequest request) {
        DailyInspectionReportController da = new DailyInspectionReportController();
        String devid = UserUtil.getSysUser().getCompanyId();//当前公司ID
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
        Date date = null;
        String nowDate = null;
        //当进行查询操作的时候，对传入的时间进行处理
        try {
            if (StringUtils.isNotBlank(lastTime_BEGIN)) {
                date = df.parse(lastTime_BEGIN);
                long ts = date.getTime();
                long now = ts + 86400000;
                Date date0 = new Date(now);
                nowDate = df.format(date0);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //根据传参不同，调用不同的方法进而获取数据
        List l1 = null;
        if (StringUtils.isBlank(nowDate) && StringUtils.isNotBlank(userName_LIKE)) {
            l1 = dirsi.searchMonthUserRoute(devid, userName_LIKE);//搜索时只输入姓名，搜索当月某人的巡线数据
        } else if (StringUtils.isBlank(nowDate) && StringUtils.isBlank(userName_LIKE)) {
            l1 = dirsi.getMonthReport(devid);//如果搜索时，时间和姓名都不输入，查询的是当月的巡线数据
        } else {
            l1 = dirsi.getMonthUserRoute(devid, userName_LIKE, nowDate);//根据时间和姓名搜索数据
        }
        TreeSet s = new TreeSet<>();//查询出巡线记录后，一个人可能会有多条巡线记录，按照姓名进行分组
        List l3 = new ArrayList();
        for (int i = 0; i < l1.size(); i++) {
            DailyInspectionReport dai = (DailyInspectionReport) l1.get(i);
            if (StringUtils.isNotBlank(dai.getUserName())) {
                s.add(dai.getUserName());
            }
        }
        //将同一人的不同巡线记录存入list中
        for (Iterator iter = s.iterator(); iter.hasNext(); ) {
            String a1Name = (String) iter.next();
            List l2 = new ArrayList();
            for (int i = 0; i < l1.size(); i++) {
                DailyInspectionReport dai = (DailyInspectionReport) l1.get(i);
                String username = dai.getUserName();
                if (StringUtils.isNotBlank(username) && StringUtils.isNotBlank(a1Name)) {
                    if (username.equals(a1Name)) {
                        l2.add(l1.get(i));
                    }
                }
            }
            l3.add(l2);
        }
        //l3为按名称进行分组的数据
        List l5 = new ArrayList();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int a = 0; a < l3.size(); a++) {
            List l4 = (List) l3.get(a);
            DailyInspectionReport dd = new DailyInspectionReport();
            double patrolTimeSum = 0;//总共巡线时间
            double count = 0;
            double countSum = 0;
            for (int b = 0; b < l4.size(); b++) {
                DailyInspectionReport dai = (DailyInspectionReport) l4.get(b);
                if (b == 0) {
                    dd.setUserName(dai.getUserName());//巡检员
                    String patrolNumber = String.valueOf(l4.size());//获取巡线次数
                    dd.setRouteName(patrolNumber);//设置巡线次数
                }
                try {
                    String getBeginTime = dai.getBeginTime();//巡线开始时间
                    String getLastTime = dai.getLastTime();//巡线结束时间
                    if (StringUtils.isNotBlank(getBeginTime) && StringUtils.isNotBlank(getLastTime)) {
                        Date beginTime = sdf.parse(dai.getBeginTime());
                        Date lastTime = sdf.parse(dai.getLastTime());
                        if (lastTime.getTime() >= beginTime.getTime()) {
                            BigDecimal b1 = new BigDecimal(beginTime.getTime());
                            BigDecimal b2 = new BigDecimal(lastTime.getTime());
                            patrolTimeSum = (b2.subtract(b1).setScale(2, RoundingMode.HALF_UP).doubleValue()) / (3600 * 1000);
                            count = patrolTimeSum + count;//当月不同时间进行累加
                        }
                    }
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                String trail = dai.getTrail();//巡线路径
                if (StringUtils.isNotBlank(trail)) {
                    JSONArray jsonArray = JSONArray.parseArray(trail);
                    for (int v = 0; v < jsonArray.size(); v++) {
                        if (v == 0) {
                            String ln = String.valueOf(jsonArray.getJSONObject(v).get("ln"));//获取路径第一个经纬度坐标
                            double lln = Double.parseDouble(ln);
                            String lt = String.valueOf(jsonArray.getJSONObject(v).get("lt"));
                            double llt = Double.parseDouble(lt);
                            llnn = lln;//存入变量，便于和下一个坐标进行计算
                            lltt = llt;
                        }
                        if (v > 0) {
                            String ln1 = String.valueOf(jsonArray.getJSONObject(v).get("ln"));//获取下一个坐标
                            double lln1 = Double.parseDouble(ln1);
                            String lt1 = String.valueOf(jsonArray.getJSONObject(v).get("lt"));
                            double llt1 = Double.parseDouble(lt1);
                            double d = da.getDistance(llnn, lltt, lln1, llt1);//根据经纬度计算距离
                            llnn = lln1;
                            lltt = llt1;
                            countSum = d + countSum;//距离累加
                        }
                    }
                    llnn = 0;
                    lltt = 0;
                }
            }
            String douStr = String.format("%.2f", countSum);
            dd.setTrail(String.valueOf(douStr));//设置总距离
            String cc = String.format("%.2f", count);
            dd.setLastTime(String.valueOf(cc));//设置总时间

            double distance = Double.parseDouble(douStr);
            double time = Double.parseDouble(cc);
            double avgSpeed = 0.0;
            if (time > 0.0) {
                avgSpeed = distance / time;
            }
            String avg = String.format("%.2f",avgSpeed);
            dd.setAverageVelocity(avg);//巡检平均速度

            l5.add(dd);
        }

        //根据不同输入参数，查询巡点数据
        List l6 = null;
        if (StringUtils.isBlank(nowDate) && StringUtils.isNotBlank(userName_LIKE)) {
            l6 = dirsi.searchMonthUserPoint(devid, userName_LIKE);
        } else if (StringUtils.isBlank(nowDate) && StringUtils.isBlank(userName_LIKE)) {
            l6 = dirsi.getMonthPoint(devid);
        } else {
            l6 = dirsi.getMonthUserPoint(devid, userName_LIKE, nowDate);
        }
        //根据姓名对巡点进行分组
        TreeSet s1 = new TreeSet<>();
        List l7 = new ArrayList();
        for (int i = 0; i < l6.size(); i++) {
            DailyInspectionReport dai = (DailyInspectionReport) l6.get(i);
            if (StringUtils.isNotBlank(dai.getUserName())) {
                s1.add(dai.getUserName());
            }
        }
        for (Iterator iter = s1.iterator(); iter.hasNext(); ) {
            String a1Name = (String) iter.next();
            List l8 = new ArrayList();
            for (int i = 0; i < l6.size(); i++) {
                DailyInspectionReport dai = (DailyInspectionReport) l6.get(i);
                String username = dai.getUserName();
                if (StringUtils.isNotBlank(username) && StringUtils.isNotBlank(a1Name)) {
                    if (username.equals(a1Name)) {
                        l8.add(l6.get(i));
                    }
                }
            }
            l7.add(l8);
        }
        List l9 = new ArrayList();
        for (int a = 0; a < l7.size(); a++) {
            List l10 = (List) l7.get(a);
            DailyInspectionReport dy = new DailyInspectionReport();
            int countType1 = 0;
            int countType2 = 0;
            int countType3 = 0;
            int countType4 = 0;
            if (l10.size() == 0) {
                dy.setStationName(String.valueOf(0));
            }
            for (int b = 0; b < l10.size(); b++) {
                DailyInspectionReport daily = (DailyInspectionReport) l10.get(b);
                if (b == 0) {
                    String patrolPointNumber = String.valueOf(l10.size());
                    dy.setStationName(patrolPointNumber);//设置巡点次数
                    dy.setUserName(daily.getUserName());//设置姓名
                }
                String typeNumber = daily.getStationType();
                if (typeNumber.equals("1")) {
                    countType1++;//主阀门次数累加
                }
                if (typeNumber.equals("2")) {
                    countType2++;//支阀门次数累加
                }
                if (typeNumber.equals("3")) {
                    countType3++;//调压柜次数累加
                }
                if (typeNumber.equals("4")) {
                    countType4++;//调压箱次数累加
                }
            }
            dy.setType1(String.valueOf(countType1));//设置主阀门次数
            dy.setType2(String.valueOf(countType2));//设置支阀门次数
            dy.setType3(String.valueOf(countType3));//设置调压柜次数
            dy.setType4(String.valueOf(countType4));//设置调压箱次数
            l9.add(dy);
        }

        da.getCombineDemo2(l9, l5);//将巡线数据和巡点数据，根据姓名进行合并
        listMonthAll = l5;//将合并后的数据，存入公共的list，便于导出Excel

        //将合并的list进行分页
        Page page = new Page(pageNo, pageSize);
        int totalLength = l5.size(); //总的数据条数
        int totalPage = totalLength % pageSize == 0 ? totalLength / pageSize : totalLength / pageSize + 1; //总的页数
        List pageData = null;
        if (pageNo < totalPage) {
            int startIndex = (pageNo - 1) * pageSize;
            int endIndex = startIndex + pageSize - 1;
            pageData = l5.subList(startIndex, endIndex + 1);
        } else {
            int startIndex = (pageNo - 1) * pageSize;
            pageData = l5.subList(startIndex, totalLength);//最后一页
        }
        page.setList(pageData);
        page.setTotal(l5.size());
        return ResultVo.success(page.getPageData());
    }

    //巡点记录和巡线记录数据合并
    public static void getCombineDemo2(List<DailyInspectionReport> demo1, List<DailyInspectionReport> demo2) {
        HashMap<String, DailyInspectionReport> map = new HashMap<>();
        //巡点有数据，巡线没数据，将巡线增加一条对应巡点的数据
        List sname1 = new ArrayList();//巡点demo1
        List sname2 = new ArrayList();//巡线demo2
        for (DailyInspectionReport sdemo1 : demo1) {
            sname1.add(sdemo1.getUserName());
        }
        for (DailyInspectionReport sdemo2 : demo2) {
            sname2.add(sdemo2.getUserName());
        }
        sname1.removeAll(sname2);
        if (sname1.size() > 0) {
            for (int i = 0; i < sname1.size(); i++) {
                DailyInspectionReport ddd = new DailyInspectionReport();
                ddd.setUserName(String.valueOf(sname1.get(i)));
                ddd.setRouteName(String.valueOf(0));
                ddd.setLastTime(String.valueOf(0));
                ddd.setTrail(String.valueOf(0));
                demo2.add(ddd);
            }
        }
        //正常数据处理，以及巡线有数据巡点没数据的处理
        for (DailyInspectionReport sameOr : demo1) {
            map.put(sameOr.getUserName(), sameOr);
        }
        for (DailyInspectionReport sameOr : demo2) {
            String sameOrNumber = sameOr.getUserName();
            DailyInspectionReport sameOr1 = map.get(sameOrNumber);
            if (sameOr1 == null) {
                sameOr.setStationName(String.valueOf(0));
                sameOr.setType1(String.valueOf(0));
                sameOr.setType2(String.valueOf(0));
                sameOr.setType3(String.valueOf(0));
                sameOr.setType4(String.valueOf(0));
                continue;
            }

            if (StringUtils.isBlank(sameOr.getUserName())) {
                sameOr.setUserName(sameOr1.getUserName());
            }
            if (StringUtils.isBlank(sameOr.getRouteName())) {
                sameOr.setRouteName(sameOr1.getRouteName());
            }
            if (StringUtils.isBlank(sameOr.getLastTime())) {
                sameOr.setLastTime(sameOr1.getLastTime());
            }
            if (StringUtils.isBlank(sameOr.getTrail())) {
                sameOr.setTrail(sameOr1.getTrail());
            }
            if (StringUtils.isBlank(sameOr.getStationName())) {
                sameOr.setStationName(sameOr1.getStationName());
            }
            if (StringUtils.isBlank(sameOr.getType1())) {
                sameOr.setType1(sameOr1.getType1());
            }
            if (StringUtils.isBlank(sameOr.getType2())) {
                sameOr.setType2(sameOr1.getType2());
            }
            if (StringUtils.isBlank(sameOr.getType3())) {
                sameOr.setType3(sameOr1.getType3());
            }
            if (StringUtils.isBlank(sameOr.getType4())) {
                sameOr.setType4(sameOr1.getType4());
            }
        }
    }

    public double EARTH_RADIUS = 6378.137;// 单位千米

    //角度弧度计算公式 rad:(),360度=2π π=Math.PI,x度 = x*π/360 弧度
    public double getRadian(double degree) {
        return degree * Math.PI / 180.0;
    }

    //依据经纬度计算两点之间的距离
    public double getDistance(double lat1, double lng1, double lat2, double lng2) {
        double radLat1 = getRadian(lat1);
        double radLat2 = getRadian(lat2);
        double a = radLat1 - radLat2;// 两点纬度差
        double b = getRadian(lng1) - getRadian(lng2);// 两点的经度差
        double s = 2 * Math.asin(Math.sqrt(Math.pow(Math.sin(a / 2), 2) + Math.cos(radLat1)
                * Math.cos(radLat2) * Math.pow(Math.sin(b / 2), 2)));
        s = s * EARTH_RADIUS;
        return s;
    }

    //导出巡检日报表Excel
    @RequestMapping(value = "/dailyExcel", method = RequestMethod.GET)
    public void getDailyRecord(HttpServletRequest request, HttpServletResponse response) {
        List l = listAll;
        String titleName = "巡检日报表";
        DailyInspectionReportController drc = new DailyInspectionReportController();
        //输出文件流
        OutputStream output = null;
        try {
            output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(("巡检日报表" + ZkUtils.getDateStr().substring(0, 10) + ".xls").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
        } catch (IOException e) {
            e.printStackTrace();
        }
        HSSFWorkbook book = null;
        try {
            book = new HSSFWorkbook(drc.getExcelFile(l, titleName));
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            book.write(output);
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            output.flush();
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            output.close();
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
    }

    //导出巡检月报表Excel
    @RequestMapping(value = "/monthExcel", method = RequestMethod.GET)
    public void getMonthRecord(HttpServletRequest request, HttpServletResponse response) {
        List l = listMonthAll;
        String titleName = "巡检月报表";
        DailyInspectionReportController drc = new DailyInspectionReportController();
        //输出文件流
        OutputStream output = null;
        try {
            output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(("巡检月报表" + ZkUtils.getDateStr().substring(0, 10) + ".xls").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
        } catch (IOException e) {
            e.printStackTrace();
        }
        HSSFWorkbook book = null;
        try {
            book = new HSSFWorkbook(drc.getExcelFile(l, titleName));
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            book.write(output);
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            output.flush();
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            output.close();
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
    }

    //导出Excel样式
    private HSSFWorkbook workbook;
    private HSSFSheet sheet;

    public InputStream getExcelFile(List l, String titleName) throws IOException {
        //获取当前系统时间
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
        String sheetName = df.format(new Date());
        //创建sheet
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
        //字体样式
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);// 字体大小
        //字体加粗样式
        HSSFFont font0 = workbook.createFont();
        font0.setFontName("宋体");
        font0.setFontHeightInPoints((short) 20);
        font0.setBold(true);
        //通用字体样式
        HSSFCellStyle cs0 = workbook.createCellStyle();//创建第一行样式
        cs0.setAlignment(HorizontalAlignment.CENTER);// 左右居中2 居右3 默认居左
        cs0.setFont(font);
        cs0.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中1
        cs0.setBorderBottom(BorderStyle.THIN);
        cs0.setBorderLeft(BorderStyle.THIN);
        cs0.setBorderRight(BorderStyle.THIN);
        cs0.setBorderTop(BorderStyle.THIN);

        //字体加粗样式
        HSSFCellStyle css0 = workbook.createCellStyle();//创建第一行样式
        css0.setAlignment(HorizontalAlignment.CENTER);
        css0.setFont(font0);

        HSSFRow h0 = sheet.createRow(0);//创建一行
        HSSFCell c0 = h0.createCell(0);//在第一行创建一个cell
        c0.setCellValue(titleName);//设置头部标题，巡检日报表或巡检月报表
        c0.setCellStyle(css0);
        CellRangeAddress cra0 = new CellRangeAddress(0, 0, 0, 8);//合并行
        sheet.addMergedRegion(cra0);
        setBorderStyle(1, cra0, sheet, workbook);
        //小区名称
        //exRow1
        HSSFRow h1 = sheet.createRow(1);//获取第二行
        HSSFCell c1 = h1.createCell(0);//巡检员
        c1.setCellValue("巡检员");
        c1.setCellStyle(cs0);
        HSSFCell c2 = h1.createCell(1);//巡线次数
        c2.setCellValue("巡线次数");
        c2.setCellStyle(cs0);
        HSSFCell c3 = h1.createCell(2);//总共巡线时间
        c3.setCellValue("总共巡线时间");
        c3.setCellStyle(cs0);
        HSSFCell c4 = h1.createCell(3);//巡线距离
        c4.setCellValue("巡线距离");
        c4.setCellStyle(cs0);
        HSSFCell c5 = h1.createCell(4);//巡点次数
        c5.setCellValue("巡点次数");
        c5.setCellStyle(cs0);
        HSSFCell c6 = h1.createCell(5);//主阀门次数
        c6.setCellValue("主阀门次数");
        c6.setCellStyle(cs0);
        HSSFCell c7 = h1.createCell(6);//支阀门次数
        c7.setCellValue("支阀门次数");
        c7.setCellStyle(cs0);
        HSSFCell c8 = h1.createCell(7);//调压柜次数
        c8.setCellValue("调压柜次数");
        c8.setCellStyle(cs0);
        HSSFCell c9 = h1.createCell(8);//调压箱次数
        c9.setCellValue("调压箱次数");
        c9.setCellStyle(cs0);
        for (int s = 0; s <= 8; s++) {
            sheet.setColumnWidth(s, 5000);
        }
        for (int h = 0; h < l.size(); h++) {
            DailyInspectionReport di = (DailyInspectionReport) l.get(h);
            int n = 2 + h;
            HSSFRow h4 = sheet.createRow(n);
            HSSFCell c01 = h4.createCell(0);
            c01.setCellValue(di.getUserName());
            c01.setCellStyle(cs0);
            HSSFCell c02 = h4.createCell(1);
            c02.setCellValue(di.getRouteName());
            c02.setCellStyle(cs0);
            HSSFCell c03 = h4.createCell(2);
            c03.setCellValue(di.getLastTime());
            c03.setCellStyle(cs0);
            HSSFCell c04 = h4.createCell(3);
            c04.setCellValue(di.getTrail());
            c04.setCellStyle(cs0);
            HSSFCell c05 = h4.createCell(4);
            c05.setCellValue(di.getStationName());
            c05.setCellStyle(cs0);
            HSSFCell c06 = h4.createCell(5);
            c06.setCellValue(di.getType1());
            c06.setCellStyle(cs0);
            HSSFCell c07 = h4.createCell(6);
            c07.setCellValue(di.getType2());
            c07.setCellStyle(cs0);
            HSSFCell c08 = h4.createCell(7);
            c08.setCellValue(di.getType3());
            c08.setCellStyle(cs0);
            HSSFCell c09 = h4.createCell(8);
            c09.setCellValue(di.getType4());
            c09.setCellStyle(cs0);
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        byte[] ba = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(ba);
        return bais;
    }

    //加边框
    private static void setBorderStyle(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb) {
        HSSFCellStyle cs = wb.createCellStyle(); // 样式对象
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        font.setBold(true);
        cs.setFont(font);
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        cs.setBorderBottom(BorderStyle.valueOf((short) border));
        cs.setBorderTop(BorderStyle.valueOf((short) border));
        cs.setBorderLeft(BorderStyle.valueOf((short) border));
        cs.setBorderRight(BorderStyle.valueOf((short) border));
        setRegionStyle(cs, region, sheet);
    }

    private static void setRegionStyle(HSSFCellStyle cs, CellRangeAddress region, HSSFSheet sheet) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
    }

}

